﻿/* *********************************
 * 项目名称：
 * 模块功能：管理员用户表信息操作
 * ---------------------------------
 * 创 建 者：
 * 创建时间：2013年12月
 * ----------------------------------
 * 更 新 者：
 * 更新日期：2013年8月10日
 * 更新内容：添加表结构获取方法GetTableStructure
 * ----------------------------------
 * 更 新 者：
 * 更新日期：
 * 更新内容：
 * *********************************/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

namespace Data
{
    /// <summary>
    /// Access数据库访问助手
    /// </summary>
    public class DbHelp
    {
        /// <summary>
        /// 根据表名获取表信息,列名[序列,字段名,字段类型,字段长度,允许空,字段描述]
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>返回Table形式表结构，列包含[序列,字段名,字段类型,字段长度,允许空,字段描述]</returns>
        public DataTable GetTableStructure(string tableName)
        {
            DataTable myTable = new DataTable();
            myTable.Columns.Add("序列");
            myTable.Columns.Add("字段名");
            myTable.Columns.Add("字段类型");
            myTable.Columns.Add("字段长度");
            myTable.Columns.Add("允许空");

            SqlConnection conn = new SqlConnection(DBConfig.ConnectionSettings);
            ConnOpen(conn);
            DataTable table = this.ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, "SELECT syscolumns.name,systypes.name as typename,syscolumns.isnullable,syscolumns.length FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id('" + tableName + "')");
            ConnClose(conn);
            int i = 0;
            foreach (DataRow dr in table.Rows)
            {
                DataRow drs = myTable.NewRow();
                drs["序列"] = i;
                drs["字段名"] = dr["name"].ToString();

                //switch (Convert.ToInt32(dr["DATA_TYPE"]))
                //{
                //    case 2: drs["字段类型"] = "int"; break;
                //    case 3: drs["字段类型"] = "int"; break;
                //    case 4: drs["字段类型"] = "Single"; break;
                //    case 5: drs["字段类型"] = "double"; break;
                //    case 6: drs["字段类型"] = "decimal"; break;
                //    case 7: drs["字段类型"] = "DateTime"; break;
                //    case 11: drs["字段类型"] = "bool"; break;
                //    case 17: drs["字段类型"] = "byte"; break;
                //    case 72: drs["字段类型"] = "string"; break;
                //    case 130: drs["字段类型"] = "string"; break;
                //    case 131: drs["字段类型"] = "decimal"; break;
                //    case 128: drs["字段类型"] = "string"; break;
                //    default: drs["字段类型"] = "string"; break;
                //}
                drs["字段类型"] = dr["typename"].ToString();
                drs["字段长度"] = dr["length"].ToString();
                drs["允许空"] = dr["isnullable"];
                myTable.Rows.Add(drs);
                i++;
            }
            return myTable;
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="conn"></param>
        public void ConnOpen(DbConnection conn)
        {
            switch (conn.State)
            {
                case ConnectionState.Broken://假死
                    conn.Close();
                    conn.Open();
                    break;
                case ConnectionState.Connecting://已连接数据源
                    break;
                case ConnectionState.Executing://正在执行命令
                    break;
                case ConnectionState.Fetching://正在检索数据
                    break;
                default://没有开启
                    conn.Open();
                    break;
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="conn"></param>
        public void ConnClose(DbConnection conn)
        {
            switch (conn.State)
            {
                case ConnectionState.Broken://假死
                    conn.Close();
                    break;
                case ConnectionState.Connecting://已连接到数据源
                    conn.Close();
                    break;
                case ConnectionState.Executing://正在执行命令
                    break;
                case ConnectionState.Fetching://正在检索数据
                    break;
                default:
                    conn.Close();
                    break;
            }
        }
        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <returns></returns>        
        public DbDataReader Read(string connSettings, CommandType commType, string commText)
        {
            SqlConnection conn = new SqlConnection(connSettings);
            try
            {
                SqlCommand cmd = new SqlCommand();
                ConnOpen(conn);
                cmd.Connection = conn;
                cmd.CommandType = commType;
                cmd.CommandText = commText;
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException ex)
            {
                ConnClose(conn);
                throw ex;
            }
        }
        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <param name="param">参数数组</param>
        /// <returns></returns>
        public DbDataReader Read(string connSettings, CommandType commType, string commText, DbParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connSettings);
            try
            {
                SqlCommand cmd = new SqlCommand();
                ConnOpen(conn);
                cmd.Connection = conn;
                cmd.CommandType = commType;

                cmd.CommandText = commText;
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException ex)
            {
                ConnClose(conn);
                throw ex;
            }
        }
        /// <summary>
        /// 读取数据集
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <returns></returns>
        public DataSet ReadDataSet(string connSettings, CommandType commType, string commText)
        {
            SqlConnection conn = new SqlConnection(connSettings);
            try
            {
                SqlCommand cmd = new SqlCommand();
                ConnOpen(conn);
                cmd.Connection = conn;
                cmd.CommandType = commType;

                cmd.CommandText = commText;

                SqlDataAdapter Dar = new SqlDataAdapter();

                Dar.SelectCommand = cmd;

                DataSet ds = new DataSet();
                Dar.Fill(ds);

                Dar.Dispose();
                cmd.Dispose();
                ConnClose(conn);

                return ds;
            }
            catch (SqlException ex)
            {
                ConnClose(conn);
                throw ex;
            }
        }
        /// <summary>
        /// 读取数据集
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <param name="param">参数数组</param>
        /// <returns></returns>
        public DataSet ReadDataSet(string connSettings, CommandType commType, string commText, DbParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connSettings);
            try
            {
                SqlCommand cmd = new SqlCommand();
                ConnOpen(conn);
                cmd.Connection = conn;
                cmd.CommandType = commType;

                cmd.CommandText = commText;
                if (param != null && param.Length > 0)
                {
                    cmd.Parameters.AddRange(param);
                }
                //创建数据适配器
                SqlDataAdapter Dar = new SqlDataAdapter();
                Dar.SelectCommand = cmd;
                //填充数据集
                DataSet ds = new DataSet();
                Dar.Fill(ds);
                //执行清理
                if (param != null && param.Length > 0)
                {
                    cmd.Parameters.Clear();
                }
                Dar.Dispose();
                cmd.Dispose();
                ConnClose(conn);

                return ds;
            }
            catch (SqlException ex)
            {
                ConnClose(conn);
                throw ex;
            }
        }
        /// <summary>
        /// 读取数据表
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <returns></returns>
        public DataTable ReadDataTable(string connSettings, CommandType commType, string commText)
        {
            SqlConnection conn = new SqlConnection(connSettings);
            try
            {
                SqlCommand cmd = new SqlCommand();
                ConnOpen(conn);
                cmd.Connection = conn;
                cmd.CommandType = commType;

                cmd.CommandText = commText;

                SqlDataAdapter Dar = new SqlDataAdapter();

                Dar.SelectCommand = cmd;

                DataSet ds = new DataSet();
                Dar.Fill(ds);

                Dar.Dispose();
                cmd.Dispose();
                ConnClose(conn);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                ConnClose(conn);
                throw ex;
            }
        }
        /// <summary>
        /// 读取数据表
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <param name="param">参数数组</param>
        /// <returns></returns>
        public DataTable ReadDataTable(string connSettings, CommandType commType, string commText, DbParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connSettings);
            try
            {
                SqlCommand cmd = new SqlCommand();
                ConnOpen(conn);
                cmd.Connection = conn;
                cmd.CommandType = commType;

                cmd.CommandText = commText;
                cmd.Parameters.AddRange(param);
                SqlDataAdapter Dar = new SqlDataAdapter();
                Dar.SelectCommand = cmd;

                DataSet ds = new DataSet();
                Dar.Fill(ds);
                cmd.Parameters.Clear();
                Dar.Dispose();
                cmd.Dispose();
                ConnClose(conn);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                ConnClose(conn);
                throw ex;
            }
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <returns></returns>
        public int Save(string connSettings, CommandType commType, string commText)
        {
            SqlConnection conn = new SqlConnection(connSettings);
            try
            {
                SqlCommand cmd = new SqlCommand();
                ConnOpen(conn);
                cmd.Connection = conn;
                cmd.CommandType = commType;

                cmd.CommandText = commText;
                int result = cmd.ExecuteNonQuery();
                cmd.Dispose();
                ConnClose(conn);
                return result;
            }
            catch (SqlException ex)
            {
                ConnClose(conn);
                throw ex;
            }
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <param name="param">参数数组</param>
        /// <returns></returns>
        public int Save(string connSettings, CommandType commType, string commText, DbParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connSettings);
            try
            {
                SqlCommand cmd = new SqlCommand();
                ConnOpen(conn);
                cmd.Connection = conn;
                cmd.CommandType = commType;

                cmd.CommandText = commText;
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                int result = cmd.ExecuteNonQuery();

                if (cmd.Parameters != null)
                {
                    cmd.Parameters.Clear();
                }
                cmd.Dispose();
                ConnClose(conn);
                return result;
            }
            catch (SqlException ex)
            {
                ConnClose(conn);
                throw ex;
            }
        }


        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <returns></returns>
        public object GetScalar(string connSettings, CommandType commType, string commText)
        {
            SqlConnection conn = new SqlConnection(connSettings);
            try
            {
                SqlCommand cmd = new SqlCommand();
                ConnOpen(conn);
                cmd.Connection = conn;
                cmd.CommandType = commType;

                cmd.CommandText = commText;
                object result = cmd.ExecuteScalar();
                cmd.Dispose();
                ConnClose(conn);
                return result;
            }
            catch (SqlException ex)
            {
                ConnClose(conn);
                throw ex;
            }
        }
        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <param name="param">参数数组</param>
        /// <returns></returns>
        public object GetScalar(string connSettings, CommandType commType, string commText, DbParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connSettings);
            try
            {
                SqlCommand cmd = new SqlCommand();
                ConnOpen(conn);
                cmd.Connection = conn;
                cmd.CommandType = commType;

                cmd.CommandText = commText;
                cmd.Parameters.AddRange(param);
                object result = cmd.ExecuteScalar();

                cmd.Parameters.Clear();

                cmd.Dispose();
                ConnClose(conn);
                return result;
            }
            catch (SqlException ex)
            {
                ConnClose(conn);
                throw ex;
            }
        }
        /// <summary>
        /// 初始化工作
        /// </summary>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
        /// <summary>
        /// 带参数的ExecuteDataSet,可以分页
        /// </summary>
        public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "dtable");
                cmd.Parameters.Clear();
                return ds;
            }
        }
        /// <summary>
        /// 带参数的ExcuteNonQuery
        /// </summary>
        public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="connSettings">数据库连接</param>
        /// <param name="commType">脚本类型</param>
        /// <param name="commText">SQL脚本</param>
        /// <param name="param">参数数组</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteDataRead(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return rdr;
                //return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
    }
}


